package com.cma.service.impl;

        import com.cma.model.db.Tcrealtime;
        import com.cma.dao.TcrealtimeRepository;
        import com.cma.model.param.HistctrackParam;
        import com.cma.model.param.Lonlat;
        import com.cma.service.TcrealtimeService;

        import org.apache.commons.logging.Log;
        import org.apache.commons.logging.LogFactory;
        import org.apache.commons.lang.StringUtils;
        import org.apache.poi.ss.formula.eval.StringValueEval;
        import org.springframework.beans.factory.annotation.Autowired;
        import org.springframework.data.domain.Page;
        import org.springframework.data.domain.PageImpl;
        import org.springframework.data.domain.Pageable;
        import org.springframework.stereotype.Service;

        import javax.persistence.EntityManager;
        import javax.persistence.PersistenceContext;
        import javax.persistence.Query;
        import javax.persistence.TypedQuery;

        import java.util.*;


/**
 * 西北太平洋热带气旋实时信息表
 * 数据库表【tcrealtime】对应的service实现类
 *
 * @author bjs
 * @date 2017-01-03 18:59:19
 */
@Service
public class TcrealtimeServiceImpl implements TcrealtimeService {

    private static final Log logger = LogFactory.getLog(TcrealtimeServiceImpl.class);
    private static final String TF_CENTER_REALTIME = "BABJ";
    //    private static final String TF_CENTER_HIS = "BJ";
    private static final String FCSTType = "BABJWTPQ";
    private static final int FCST_HOUT = 0;
    @Autowired
    private TcrealtimeRepository tcrealtimeRepository;


    @PersistenceContext
    private EntityManager entityManager;


    //===================================================================================================

    /**
     * 保存或更新对象
     *
     * @param tcrealtime
     */
    @Override
    public void save(Tcrealtime tcrealtime) {
        tcrealtimeRepository.save(tcrealtime);
    }


    /**
     * 通过id查找对象
     *
     * @param id
     * @return
     */
    @Override
    public Tcrealtime findById(Long id) {
        return tcrealtimeRepository.findOne(id);
    }


    /**
     * 提供给管理使用的分页查询
     *
     * @param pageable
     * @return
     */
    @Override
    public Page<Tcrealtime> findForAdminList(Pageable pageable) {
        StringBuffer sqlForCount = new StringBuffer("select count(c.id) from Tcrealtime c where 1=1 ");
        StringBuffer sqlForList = new StringBuffer("select c from Tcrealtime c where 1=1 ");

        /*
        if (StringUtils.isNotBlank(name)) {
            sqlForCount.append(" and ( c.name like  '%" + name + "%' or c.username like '%" + name + "%' )  ");
            sqlForList.append(" and ( c.name like  '%" + name + "%' or c.username like '%" + name + "%' )  ");
        }
        */

        sqlForList.append(" order by c.id desc ");

        TypedQuery queryForCount = entityManager.createQuery(sqlForCount.toString(), Long.class);
        Long count = (Long) queryForCount.getSingleResult();

        TypedQuery queryForList = entityManager.createQuery(sqlForList.toString(), Tcrealtime.class);
        List<Tcrealtime> adminList = queryForList.setMaxResults(pageable.getPageSize()).setFirstResult(pageable.getOffset()).getResultList();

        Page<Tcrealtime> pageRst = new PageImpl<Tcrealtime>(adminList, pageable, count);
        return pageRst;
    }

    @Override
    public List<Tcrealtime> findPathData(String xuhao) {
        StringBuffer sqlForList = new StringBuffer("select c from Tcrealtime c where c.center ='BABJ'  and c.FCSTType='BABJWTPQ'");

        sqlForList.append(" and c.tfbh = '" + xuhao + "'");

        sqlForList.append(" order by  c.datetime,c.fcsthour");

        TypedQuery queryForList = entityManager.createQuery(sqlForList.toString(), Tcrealtime.class);
        List<Tcrealtime> list = queryForList.getResultList();

        List<Tcrealtime> resultList = new ArrayList<Tcrealtime>();
        // 处理数据
        int n = list.size();
        int j = 0;
        for (int i=0;i<n;i++) {
            Tcrealtime t = list.get(i);
            Integer fcsthour = t.getFcsthour();
            if(fcsthour.intValue() == 0){
                t.setForcastDataList(new ArrayList<Tcrealtime>());
                resultList.add(t);
                j++;
            }else{
                resultList.get(j-1).getForcastDataList().add(t);
            }
        }

        // TODO 获取美国预报数据
        Map<String,List<Tcrealtime>> map_PGTW=  find_PGTW_PathData(xuhao);
        //TODO 获取日本预报数据
        Map<String,List<Tcrealtime>> map_RJTD =find_RJTD_PathData(xuhao);
        List<Tcrealtime> result_YB_List=new ArrayList<>();
        if(resultList!=null&&resultList.size()>0){
            for(int i=0;i<resultList.size();i++){
                Tcrealtime tc=resultList.get(i);
                if(map_PGTW.containsKey(tc.getDatetime())){
                    tc.setForcast_PGTW_DateList(map_PGTW.get(tc.getDatetime()));
                }
                if(map_RJTD.containsKey(tc.getDatetime())){
                    tc.setForcast_RJTD_DateList(map_RJTD.get(tc.getDatetime()));
                }
                result_YB_List.add(tc);
            }
        }
        return result_YB_List;
    }

    /**
     * 获取最新的实时的台风
     *
     * @return
     */
    @Override
    public List<Tcrealtime> findCrealtieData() {
        //获取当前最大日期
        StringBuffer sqlForList = new StringBuffer("select max(c.datetime) from tcrealtime c where c.center ='BABJ'  and c.FCSTType='BABJWTPQ'");
        Query queryForcount=entityManager.createNativeQuery(sqlForList.toString());
        List<String> temp=queryForcount.getResultList();
        String maxtime=temp.get(0);//获取最大的时间
        StringBuffer sqlForList2 = new StringBuffer("select c from Tcrealtime c where c.center ='BABJ'  and c.FCSTType='BABJWTPQ' and c.datetime in('"+maxtime+"') ");
        TypedQuery queryForList = entityManager.createQuery(sqlForList2.toString(),Tcrealtime.class);
        List<Tcrealtime> list = queryForList.getResultList();
        Tcrealtime c=list.get(0);
        String tfbh=c.getTfbhbabj();
        String sqlFrolist3=new StringBuffer("select c from Tcrealtime c where c.center ='BABJ'  and c.FCSTType='BABJWTPQ' and c.tfbhbabj='"+tfbh+"'  order by c.datetime,c.fcsthour ").toString();
        TypedQuery queryForList3 = entityManager.createQuery(sqlFrolist3.toString(),Tcrealtime.class);
        List<Tcrealtime> list2 = queryForList3.getResultList();
        List<Tcrealtime> resultList = new ArrayList<Tcrealtime>();
        // 处理数据
        int n = list2.size();
        int j = 0;
        for (int i=0;i<n;i++) {
            Tcrealtime t = list2.get(i);
            Integer fcsthour = t.getFcsthour();
            if(fcsthour.intValue() == 0){
                t.setForcastDataList(new ArrayList<Tcrealtime>());
                resultList.add(t);
                j++;
            }else{
                resultList.get(j-1).getForcastDataList().add(t);
            }
        }
        if(resultList!=null&&resultList.size()>0){
            for(int i=0;i<resultList.size();i++){
                Tcrealtime tl=resultList.get(i);
            }
        }
        // TODO 获取美国预报数据
        Map<String,List<Tcrealtime>> map_PGTW=  find_PGTW_PathData(tfbh);
        //TODO 获取日本预报数据
        Map<String,List<Tcrealtime>> map_RJTD =find_RJTD_PathData(tfbh);
        List<Tcrealtime> result_YB_List=new ArrayList<>();
        if(resultList!=null&&resultList.size()>0){
            for(int i=0;i<resultList.size();i++){
                Tcrealtime tc=resultList.get(i);
                if(map_PGTW.containsKey(tc.getDatetime())){
                    tc.setForcast_PGTW_DateList(map_PGTW.get(tc.getDatetime()));
                }
                if(map_RJTD.containsKey(tc.getDatetime())){
                    tc.setForcast_RJTD_DateList(map_RJTD.get(tc.getDatetime()));
                }
                result_YB_List.add(tc);
            }
        }
        return result_YB_List;
    }

    /**
     * 获取实时库中 历年的台风最大风速
     * @param histctrackParam
     * @return
     */
    @Override
    public Map<String, String> getMaxFs(HistctrackParam histctrackParam) {
        Map<String,String> map=new LinkedHashMap<>();
        String sql="SELECT max(windv) as windv,date_format(datetime,'%Y') as date,center,fcsthour,FCSTType FROM tcrealtime WHERE center='BABJ' and fcsthour=0 and  FCSTType='BABJWTPQ' " ;
        //如果经纬度 和经纬度范围半径都存在的话进行筛选
        if(!StringUtils.isEmpty(histctrackParam.getLon())&&!StringUtils.isEmpty(histctrackParam.getLon())&&!StringUtils.isEmpty(histctrackParam.getLlWidth())){
            sql+="  and sqrt(((("+histctrackParam.getLon()+"-lon)*PI()*12656*cos((("+histctrackParam.getLat()+"+lat)/2)*PI()/180)/180)*" +
                    "(("+histctrackParam.getLon()+"-lon)*PI()*12656*cos((("+histctrackParam.getLat()+"+lat)/2)*PI()/180)/180))+" +
                    "((("+histctrackParam.getLat()+"-lat)*PI()*12656/180)*(("+histctrackParam.getLat()+"-lat)*PI()*12656/180)))<"+histctrackParam.getLlWidth();
        }
             sql+=" GROUP BY DATE_FORMAT(datetime,'%Y'),center,fcsthour,FCSTType";
        Query queryForcount=entityManager.createNativeQuery(sql);
        List<Object[]> temp=queryForcount.getResultList();
        try{
            if(temp.size()>0){
                for (int i=0;i<temp.size();i++){
                    map.put(String.valueOf(temp.get(i)[1]),String.valueOf(temp.get(i)[0].toString()));
                }
            }
        }catch (Exception e){
            e.printStackTrace();
        }

        return map;
    }

    /**
     * 获取实时库中 台风强度的统计
     * @param histctrackParam
     * @return
     */
    @Override
    public Map<String, String> getMaxStrength(HistctrackParam histctrackParam) {
        Map<String,String> map=new HashMap<>();
        String sql="SELECT count(strength) as count,strength  FROM tcrealtime WHERE center='BABJ' and fcsthour=0 and  FCSTType='BABJWTPQ' " ;
        if(!StringUtils.isEmpty(histctrackParam.getLon())&&!StringUtils.isEmpty(histctrackParam.getLon())&&!StringUtils.isEmpty(histctrackParam.getLlWidth())){
            sql+=" and  sqrt(((("+histctrackParam.getLon()+"-lon)*PI()*12656*cos((("+histctrackParam.getLat()+"+lat)/2)*PI()/180)/180)*" +
                    "(("+histctrackParam.getLon()+"-lon)*PI()*12656*cos((("+histctrackParam.getLat()+"+lat)/2)*PI()/180)/180))+" +
                    "((("+histctrackParam.getLat()+"-lat)*PI()*12656/180)*(("+histctrackParam.getLat()+"-lat)*PI()*12656/180)))<"+histctrackParam.getLlWidth();
        }
        sql+=" GROUP BY strength";
        Query queryForcount=entityManager.createNativeQuery(sql);
        List<Object[]> temp=queryForcount.getResultList();
        try{
            if(temp.size()>0){
                for (int i=0;i<temp.size();i++){
                    map.put(String.valueOf(temp.get(i)[1]),String.valueOf(temp.get(i)[0].toString()));
                }
            }
        }catch (Exception e){
            e.printStackTrace();
        }

        return map;
    }

    @Override
    public List<Tcrealtime> findPathDatas(String newbh) {
        StringBuffer sqlForList = new StringBuffer("select c from Tcrealtime c where c.center ='BABJ'  and c.FCSTType='BABJWTPQ'");

        sqlForList.append(" and c.tfbhbabj in(" + newbh + ")");

        sqlForList.append(" order by  c.handletime,c.fcsthour");

        TypedQuery queryForList = entityManager.createQuery(sqlForList.toString(), Tcrealtime.class);
        List<Tcrealtime> list = queryForList.getResultList();
        return list;
    }

    /**
     * 根据编号与时间点 --- 获取美国综合预报路径
     *
     * @param tfbh     台风编号
     * @return
     */
    @Override
    public Map<String,List<Tcrealtime>> find_PGTW_PathData(String tfbh) {
        StringBuffer sqlForList = new StringBuffer("select c from Tcrealtime c where c.center ='PGTW'  and c.FCSTType='PGTWSUBJ'");

        sqlForList.append(" and c.tfbh ='" + tfbh + "' and c.fcsthour>0 ");

        sqlForList.append(" order by  c.fcsthour");

        TypedQuery queryForList = entityManager.createQuery(sqlForList.toString(), Tcrealtime.class);
        List<Tcrealtime> list = queryForList.getResultList();
        Map<String,List<Tcrealtime>> map=new HashMap<>();
        if(list!=null&&list.size()>0){
            for(int i=0;i<list.size();i++){
                if(map.containsKey(list.get(i).getDatetime())){
                    map.get(list.get(i).getDatetime()).add(list.get(i));
                }else{
                    List<Tcrealtime> list2=new ArrayList<>();
                    list2.add(list.get(i));
                    map.put(list.get(i).getDatetime(),list2);
                }
            }
        }

        return map;
    }

    /**
     * 根据编号与时间点 --- 获取日本综合预报路径
     *
     * @param tfbh     台风编号
     * @return
     */
    @Override
    public Map<String,List<Tcrealtime>> find_RJTD_PathData(String tfbh) {
        StringBuffer sqlForList = new StringBuffer("select c from Tcrealtime c where c.center ='RJTD'  and c.FCSTType='RJTDSUBJ'");
        sqlForList.append(" and c.tfbh ='" + tfbh + "' and c.fcsthour>0 ");
        sqlForList.append(" order by  c.fcsthour");
        TypedQuery queryForList = entityManager.createQuery(sqlForList.toString(), Tcrealtime.class);
        List<Tcrealtime> list = queryForList.getResultList();
        Map<String,List<Tcrealtime>> map=new HashMap<>();
        if(list!=null&&list.size()>0){
            for(int i=0;i<list.size();i++){
                if(map.containsKey(list.get(i).getDatetime())){
                    map.get(list.get(i).getDatetime()).add(list.get(i));
                }else{
                    List<Tcrealtime> list2=new ArrayList<>();
                    list2.add(list.get(i));
                    map.put(list.get(i).getDatetime(),list2);
                }
            }
        }

        return map;
    }

    /**
     * 根据最大最小经纬度 获取对应的台风点集合
     * @param maxLonlat
     * @param minLonlat
     * @return
     */
    public List<Lonlat> findDataByRect(Lonlat maxLonlat, Lonlat minLonlat) {
        String sql="select lon,lat,'new' as ty,tfbh from tcrealtime where center ='"+TF_CENTER_REALTIME+"'  and FCSTType='"+FCSTType+"' and fcsthour="+FCST_HOUT+"";
        sql +=" and lat<="+maxLonlat.getLat()+" and lat>="+minLonlat.getLat()+" and lon<="+maxLonlat.getLon()+" and lon>="+minLonlat.getLon();
       try {

           Query query= entityManager.createNativeQuery(sql);
           List<Object[]> list=query.getResultList();
           List<Lonlat> listLatLon=new ArrayList<>();
           if(list.size()>0){
               for (int i=0;i<list.size();i++){
                   Lonlat lonlat=new Lonlat();
                   lonlat.setLon(String.valueOf(list.get(i)[0]));
                   lonlat.setLat(String.valueOf(list.get(i)[1]));
                   lonlat.setTy(String.valueOf(list.get(i)[2]));
                   if(list.get(i)[3]!=null){
                       lonlat.setTfbh(String.valueOf(list.get(i)[3]));
                   }
                   listLatLon.add(lonlat);
               }
           }
           return listLatLon;
       }catch (Exception e){
           e.printStackTrace();
       }
         return null;
    }

    @Override
    public Map<String, String> getStrengthStat(String substring) {
        Map<String,String> map=new HashMap<>();
        String sql="SELECT count(strength) as count,strength  FROM tcrealtime WHERE center='BABJ' and fcsthour=0 and  FCSTType='BABJWTPQ' " ;
        sql+=" and tfbh in("+substring+")";
        sql+=" GROUP BY strength";
        Query queryForcount=entityManager.createNativeQuery(sql);
        List<Object[]> temp=queryForcount.getResultList();
        try{
            if(temp.size()>0){
                for (int i=0;i<temp.size();i++){
                    map.put(String.valueOf(temp.get(i)[1]),String.valueOf(temp.get(i)[0].toString()));
                }
            }
        }catch (Exception e){
            e.printStackTrace();
        }

        return map;
    }


}
